import xlwings as xw
import re,json
import pandas as pd

def 重建一张表(tabName,wb):

    if tabName  in  [i.name for i in wb.sheets] :
        wb.sheets[ tabName ].delete()
    wb.sheets.add( tabName,after = wb.sheets['配置信息'])
    return wb.sheets[tabName]

def e读取各个文件统计设备():
    """  """
    wb = xw.Book.caller()
    tabName = '统计设备'
    
    tab = 读取各个文件统计设备()
    
    #--写入表
    sheet = 重建一张表(tabName,wb)

    sheet.range('A1').expand().options(pd.DataFrame).value = tab
    ## 美化
    rowNum = sheet.used_range.last_cell.row
    rankNum = sheet.used_range.last_cell.column
    s首行锁定(wb,sheet)
    s设置单元格大小( sheet.range((1,1),(rowNum+1,rankNum+1))  )
    s设置单元格样式( sheet.range((1,1),(1,rankNum)) )
    s设置隔行背景(sheet)
    简报(tab,tabName+'report')

def 简报(tab,tabName):
    wb = xw.Book.caller()
    sheet = 重建一张表(tabName ,wb)
    #----简报
    tab['数量'] = 1
    retab = tab.groupby(['设备']).count().sort_values(by=['数量'],  ascending=False).loc[:,['数量']]
    sheet.range('A1').expand().options(pd.DataFrame).value = retab.head(5)
    
    ## 美化
    rowNum = sheet.used_range.last_cell.row
    rankNum = sheet.used_range.last_cell.column
    s首行锁定(wb,sheet)
    s设置隔行背景(sheet)
    s设置单元格大小( sheet.range((1,1),(rowNum+1,rankNum+1))  )
    # 图表
    chart = sheet.charts.add(150,50)
    chart.set_source_data(sheet.range('A1').expand())
    # 设置图表类型为Column
    chart.chart_type = 'column_clustered'
    # 下面对图表美化
    chart.api[1].Axes(1).HasTitle = True
    chart.api[1].Axes(2).HasTitle = True
    chart.api[1].Axes(1).AxisTitle.Text = '设备类型'
    chart.api[1].Axes(2).AxisTitle.Text = '数量'
    chart.api[1].SeriesCollection(1).Name = '基于ip设备统计'


    
def 读取各个文件统计设备():
    #读取arp
    file = r"D:\jack\notes\05-07\OpenWrt_192.168.20.1_arp.json"
    with open(file,'r',encoding='utf8')as fp:
        json_data = json.load(fp)
    dfarp = pd.DataFrame(json_data)

    # --监控主机中的设备
    app=xw.App(visible=True,add_book=False)
    filepath=r'C:\Users\Administrator\ipynb\一键excel\海康监控管理.xlsm'
    #filepath=file = wb.sheets['配置信息'].range('b2').value
    wb=app.books.open(filepath)
    sheet = wb.sheets['摄像头通道汇总']
    values = sheet.used_range.value
    df主机导出监控1 = pd.DataFrame(values[1:],columns=values[0:1])
    df主机导出监控1['设备1'] = '监控'
    df主机导出监控1.columns = [ col[0] for col in df主机导出监控1.columns]
    wb.close()
    

    #---广播监控
    csvfile = r'D:\jack\notes\05-07\129.csv'
    df广播监控2 =   pd.read_csv(csvfile,encoding='gbk')
    df广播监控2['设备2'] = '监控'
    df广播监控2.columns = [col for col in df广播监控2.columns ]

    #--扫描结果
    dfipos = 读取nmap的xml扫描文件()
    # 合并
    df2 = pd.merge(dfarp,df广播监控2.loc[:,['IP地址','设备2']],how='outer',left_on = 'ip',right_on="IP地址")
    df2['ip'] = df2.apply(lambda z: z['ip'] if pd.isnull(z['IP地址']) else z['IP地址'] ,axis=1)
    df2 = pd.merge(df2, df主机导出监控1.loc[:, ['ip地址','设备1']] ,how='outer',left_on = 'ip',right_on="ip地址" )
    df2['ip'] = df2.apply(lambda z: z['ip'] if pd.isnull(z['ip地址']) else z['ip地址'] ,axis=1)
    df2['设备'] = df2.apply(lambda z: z['设备2'] if pd.isnull(z['设备1']) else z['设备1'] ,axis=1)
    df2 = pd.merge(df2,dfipos ,how="outer",on='ip')
    
    # 最后-写入
    df2 = df2.loc[:,['ip','设备','os','扫描结果']].groupby(['ip']).first() #去重
    df2['设备'] = df2.apply(lambda z: 'windows pc' if 'Windows' in str(z['os'])  else z['os']  if pd.isnull(z['设备']) else z['设备'] ,axis=1)
 
    
    df2.sort_values(by=['设备','ip'], inplace=True ,ascending=False)
    return  df2

def 读取主机ip和os(xjson):
    """  解析 json 读取IP os类型
    nmap --host-timeout 1000 -T 5 -O -oX outv11.xml 192.168.11.*
    """
    ls = []
    for host in xjson['nmaprun']['host']:
        r = []
        if host['address']  and type(host['address']).__name__ == 'list':
            #print('2IP==',host['address'][0] )
            r.append(host['address'][0]['@addr'])
            if host['os'] and host['os'].get('osmatch')  and type(host['os']['osmatch']).__name__ == 'list':
                #print('1OS==',host['os']['osmatch'][0]['@name'])
                r.append( host['os']['osmatch'][0]['@name'] )
            elif host['os'] and  host['os'].get('osmatch')  and host['os']['osmatch']['@name']:
                #print('1==OS==',host['os']['osmatch']['@name'])
                r.append( host['os']['osmatch']['@name'] )
            else:
                r.append( "未知" )
        if len(r):
            ls.append(r)
    return ls


 
def xml_to_json(xml_str):
    import xmltodict
    # parse是的xml解析器
    xml_parse = xmltodict.parse(xml_str)
    # json库dumps()是将dict转化成json格式,loads()是将json转化成dict格式。
    # dumps()方法的ident=1,格式化json
    json_str = json.dumps(xml_parse, indent=1)
    return json_str
 

def 读取nmap的xml扫描文件():
    """  """
    xmlLs = [6,11,15,17,20,34,87,88]
    als = []
    for x in xmlLs:
        with open('D:\jack\监控导出\outv{}.xml'.format(x),'r',encoding='utf-8') as f:
            content = f.read()
            xjson = xml_to_json(content)
            xjson = json.loads(xjson)
            als = als + 读取主机ip和os(xjson)
    ipos = pd.DataFrame(als,columns=['ip','os'])
    ipos['扫描结果'] = '扫描结果'
    return  ipos

def e读取交换机macvlan():
    ## 当前的excel
    wb = xw.Book.caller()
    tabName = 'sw3700-macvlan'
    file = wb.sheets['配置信息'].range('b2').value
    contents3700 = 读取输出文件内容(file)
    vlanDc = 读取配置3700macvlan(contents3700)
    newMacLan = 转到maclan的格式(vlanDc)
    # 写入表格
    sheet =  重建一张表(tabName,wb)
    thead =  ['绑定 mac vlan ','mac','vlan']
    sheet.range('A1').options().value  =thead
    #sheet.range('A2').options(transpose=True).value = newMacLan
    sheet.range('A2').options( expand='table') .value = newMacLan
    sheet.range('A1:A2').columns.autofit()
    # 设置样式
    s设置单元格大小( sheet.range((1,1),(len(newMacLan),len(thead)))  )
    s设置单元格样式( sheet.range((1,1),(1,len(thead))) )
    s设置隔行背景(sheet)
    s首行锁定(wb,sheet)
     
def e创建端口地址报表(cellNum):
    
    def 读取端口地址数据(file):
        with open(file,'r',encoding='utf8')as fp:
            json_data = json.load(fp)
        df = pd.DataFrame(json_data)
        gpdf = df.groupby(['port','vlan']).count() #聚合求个数
        return gpdf
            
    wb = xw.Book.caller()
    tabName = wb.sheets['配置信息'].range("A{}".format(cellNum)).value
    file = wb.sheets['配置信息'].range("B{}".format(cellNum)).value
    # 生成数据
    gpdf = 读取端口地址数据(file)
    # 写入表格
    sheet = 重建一张表(tabName,wb)
    sheet.range('A1').expand().options(pd.DataFrame).value = gpdf
    ## 美化
    rowNum = sheet.used_range.last_cell.row
    rankNum = sheet.used_range.last_cell.column

    s设置单元格大小( sheet.range((1,1),(rowNum+1,rankNum+1))  )
    s设置单元格样式( sheet.range((1,1),(1,rankNum)) )
    s设置隔行背景(sheet)
    s上下去重(sheet,1)
    s首行锁定(wb,sheet)
    #  二级按钮
    shape = sheet.api.Shapes.AddShape(1, 200, 1, 55, 15 )
    sheet.shapes[-1].api.OnAction = "e查询端口mac明细"

def e查询端口mac明细(cellNum):
    """ 读取当前的 选中作为筛选条件 """
    wb = xw.Book.caller()
    ls = wb.selection.value 
    #-----
    tabName = '查询端口mac'
    file = wb.sheets['配置信息'].range("B{}".format(cellNum)).value
    
    with open(file,'r',encoding='utf8')as fp:
        json_data = json.load(fp)
    df = pd.DataFrame(json_data)
    # 筛选
    sheet =  重建一张表(tabName,wb)
    if not (ls is not None and type(ls).__name__== 'list' and len(ls) >= 2):
        sheet.range((1,1)).value = '请选中正确端口和vlan后点击按钮'
        return 
    if type(ls).__name__== 'list' and type(ls[0]).__name__== 'list':#二维数据
        sPort = ls[0][0]
        sVlan = ls[-1][-1]
    else:
        sPort , sVlan = ls #一维数据
    tab = df[(df['port']==sPort ) &  (df['vlan']=='{:g}'.format( sVlan ) ) ]
    sheet.range('A1').expand().options(pd.DataFrame, index=False).value = tab
    #美化
    rowNum,rankNum = tab.shape
    s设置单元格大小( sheet.range((1,1),(rowNum+1,rankNum+1))  )
    s设置单元格样式( sheet.range((1,1),(1,rankNum)) )
    s设置隔行背景(sheet)
    s上下去重(sheet,1)
    s首行锁定(wb,sheet)



def s首行锁定(wb,sheet):
    wb.app.api.ActiveWindow.FreezePanes = False
    sheet.range((2,1)).select()#可活动的单元格
    wb.app.api.ActiveWindow.FreezePanes = True

def e读取交换机接口配置3700():
    wb = xw.Book.caller()
    file = wb.sheets['配置信息'].range('b2').value
    e读取交换机接口配置(file,'sw3700-port')

def e读取交换机接口配置5560():
    wb = xw.Book.caller()
    file = wb.sheets['配置信息'].range('b3').value
    e读取交换机接口配置(file,'sw5560-port')


def e读取交换机接口配置(file,tabName):
    """ 端口 类型 主vlan  允许vlan """
    wb = xw.Book.caller()
    contents = 读取输出文件内容(file)

    # 读取数据
    p = re.compile(r'interface [a-zA-Z]+[0-9]+/[0-9]+/[0-9]+[\s\n]+[^#]+', re.DOTALL)
    portList = p.findall( ''.join(contents) )  
    thead = ['端口','类型 ','主vlan']
    tbody  = []
    for i in portList:
        tbody.append(端口类型判断(i))
    # 写入表格
    sheet =  重建一张表(tabName,wb)
    sheet.range('A1').options().value = thead
    sheet.range('A2').options( expand='table') .value = tbody
    # 设置样式
    s设置单元格大小( sheet.range('A1:C{}'.format(len(tbody)))  )
    s设置单元格样式( sheet.range('A1:C1') )
    s设置隔行背景(sheet)
    s首行锁定(wb,sheet)
 
def s设置单元格大小(cell):
    
    cell.columns.autofit()
    

def s设置单元格样式(cell):
    """ 可以批量 """
    cell.api.HorizontalAlignment = -4108
    cell.api.Font.Bold = True 

def s设置隔行背景(sheet):
    rowNum = sheet.used_range.last_cell.row
    rankNum = sheet.used_range.last_cell.column
    colorNum = (250,240,230)
    for i  in range(1,rowNum+1)  :
        if (i % 2)==0 :
            sheet.range((i,1),(i,rankNum) ).color = colorNum
    
def s上下去重(sheet,col=1):
    rowNum = sheet.used_range.last_cell.row
    cellVal = ''
    for i in range(1,rowNum+1):
        cell =  sheet.range((i,col) )
        if cellVal == cell.value:
            #sheet.range((i,col) ).clear()# 改成与背景同色
            cell.api.Font.Color = cell.api.Interior.Color #背景同色
        else:
            cellVal =  cell.value
 
    
def 读取输出文件内容(file=None):
    """ """
    if file is None :
        file = wb.sheets['配置信息'].range('b2').value
    with open(file, 'r') as file:
        contents3700 = file.readlines()
    return contents3700



def 读取配置3700macvlan(contents3700):
    # 返回dict key等于vlan number
    vlanDc = {}
    inout = 0
    for i in contents3700:
        ## 跳过无效行
        if i == "\n":
            continue
        research = re.search('^vlan (\d+)$',i)
        ## 开始读取
        if research is not None:
            ## 切换
            macVlan = []
            inout = 1 
            vlanDc[research[1]] = macVlan
            continue
            #print(research,research[1])
        # 停止读取
        if inout == 1  and i == "#\n":
            inout = 0
            #print( i )
            break

        if inout == 1:
            ## 写入
            macVlan.append(i)
    return vlanDc

def 转到maclan的格式(vlanDc):
    # 原来  mac-vlan mac-address 001f-c603-1f09 priority 0
    # 转成  mac-vlan mac-address 4cbd-8f90-7198 vlan 88
    # 两层循环  返回list
    newMacLan = []
    for k in vlanDc:
        oneVlan = vlanDc[k]
        for t in  oneVlan:
            #re.sub(pattern, repl, string, count=0, flags=0)
            newRow =  re.sub(' priority.*',' vlan {}'.format(k),t)  
            research = re.search ('mac-vlan mac-address ([a-zA-Z0-9-]+)',t )
            if research  is not None :
                macaddr = research[1]
            else:
                macaddr = ''
            newMacLan.append([newRow.strip(),macaddr,k])
        #print(k)
    return newMacLan

def 端口类型判断(portContent):
    """ h3c huawei通用 """
    portType  = 'unknown'
    if '\n port link-type trunk' in portContent:
        portType =  'trunk'
    if '\n port hybrid pvid vlan' in portContent or "\n port link-type hybrid" in portContent:
        portType =  'hybrid'
    if '\n port access vlan' in portContent or "\n port link-type access"  in portContent :
        portType =  'access'
    # 端口 ，默认类型，主vlan号
    """
    port access vlan 20
    port default vlan 20
    
    port hybrid pvid vlan 17
    port hybrid pvid vlan 17

    port trunk pvid vlan 13
    port trunk pvid vlan 2

    """
    pvid = 0 
    if  portType ==  'trunk':
        research = re.search ('\n port trunk pvid vlan\s+(\d+)\n',portContent)
        if research  is not None :
            pvid = research[1]
        
    if  portType ==  'hybrid':
        research = re.search ('\n port hybrid pvid vlan\s+(\d+)\n',portContent)
        if research  is not None :
            pvid = research[1]
    if  portType ==  'access':
        research = re.search ('\n port (default|access) vlan\s+(\d+)\n',portContent)
        if research  is not None :
            pvid = research[2]
    ## 端口名称
    portName = '未知'
    research = re.search ('interface ([a-zA-Z]+[0-9]+/[0-9]+/[0-9]+)\n',portContent)
    if research  is not None :
        portName = research[1]
    return portName,portType,pvid
    

if __name__ == "__main__":
    xw.Book("myproject.xlsm").set_mock_caller()
    
